package com.cennavi.bigdata.baotou.scripts;


import com.alibaba.fastjson.JSONObject;
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.HanyuPinyinVCharType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;
import org.apache.commons.dbcp.BasicDataSource;
import org.junit.Test;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.*;

import static java.lang.System.out;

/**
 * Created by cennavi on 2017/10/19.
 */
public class ChangeFileContent {

    public void closeOracle(ResultSet result, PreparedStatement pre, Connection con) {
        try {
            // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
            // 注意关闭的顺序，最后使用的最先关闭
            if (result != null)
                result.close();
            if (pre != null)
                pre.close();
            if (con != null)
                con.close();
            out.println("数据库连接已关闭！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection(){
        Connection connection = null;
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
        basicDataSource.setUrl("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=15.32.12.203)(PORT=1521))(ADDRESS = (PROTOCOL = TCP)(HOST =15.32.12.203 )(PORT = 1521))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=jczhdb)))");
        basicDataSource.setUsername("btjczh");
        basicDataSource.setPassword("btjczh1314");
        try {
            connection = basicDataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("数据库连接成功");
        return connection;
    }

//    @Test

    /**
     * 根据文件生成数据库静态表
     */
    @Test
    public void changeFile() {
        File fileIn = new File("G:\\BaoTou\\201802\\Segment_Merge_baotou.csv");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Connection connection3 = null;
        PreparedStatement statement3 = null;
        ResultSet resultSet3 = null;
        try {
            StringBuffer buffer = new StringBuffer();
            BufferedReader reader = new BufferedReader(new FileReader(fileIn));
            JSONObject jsonObjects = new JSONObject();
            List<String> strings = new ArrayList<>();
            JSONObject commJson = new JSONObject();
            String sql2 = "select id,roadname,startname,endname from INDEX_ROAD_INFO ";
            connection3 = getConnection();
            statement3 = connection3.prepareStatement(sql2);
            resultSet3 = statement3.executeQuery();
            while (resultSet3.next()){
                String roadid = resultSet3.getString("id");
                String roadname = resultSet3.getString("roadname");
                String startname = resultSet3.getString("startname");
                String endname = resultSet3.getString("endname");
                String  roadApplication = roadname + "(" +startname + "," + endname + ")";
                commJson.put(roadApplication,roadid);
            }
            while ((buffer.append(reader.readLine()) ) != null) {
                    System.out.println(buffer.toString());
                    String[] split = buffer.toString().split(",");
                        System.out.println(split.length);
                        if (buffer.toString().equals("null")){
                            break;
                        }
                        String[] split1 = split[1].split("\"");
                        String[] split4 = split[4].split("\"");
                        String[] split5 = split[5].split("\"");
                        String[] split6 = split[6].split("\"");
                        String[] split3 = split[3].split("\"");
                        String[] split7 = split[7].split("\"");
                        String[] split8 = split[8].split("\"");
                        String[] split9 = split[9].split("\"");
                        String roadapplication = split1[1] + "(" +split5[1] + "," + split6[1] + ")";//路段名称
                    if (split9.length>1&&commJson.containsKey(roadapplication)){
                        String roadName = split1[1]+"," + split5[1] +","+ split6[1];
                        String roadid = commJson.getString(roadapplication);//路段编号
                        String roaddirection = split3[1];//路段方向
                        String serialNumber = split4[1];//路段序号
                        double roadlength = Double.parseDouble(split8[1]);//路段长度
                        String roadstartendlont = split9[1];//路段起始点坐标
                        String kind = split7[1].split(" ")[0].substring(split7[1].split(" ")[0].length()-5,split7[1].split(" ")[0].length()-4);
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("roadid",roadid);
                        jsonObject.put("roadapplication",roadapplication);
                        jsonObject.put("roaddirection",roaddirection);
                        jsonObject.put("roadlength",roadlength);
                        jsonObject.put("roadstartendlont",roadstartendlont);
                        jsonObject.put("roadName",roadName);
                        jsonObject.put("kind",kind);
                        jsonObject.put("serialNumber",serialNumber);
                        jsonObjects.put(roadid,jsonObject);
                        strings.add(roadid);
                    }
                    buffer = new StringBuffer();
                }
            reader.close();

            String  sql1 = "insert into ROAD_PIECEWISE_DATA (ID,ROADAPPLICATION,ROADDIRECTION,ROADLENGTH,ROADSTARTENDLONT,ROADNAME,KIND，SERIALNUMBER)" +
                    " VALUES (?,?,?,?,?,?,?,?)";

                connection  = getConnection();
                statement = connection.prepareStatement(sql1);
            DecimalFormat df = new DecimalFormat("#.###");
            for (String string : strings) {
                JSONObject jsonObject  = (JSONObject) jsonObjects.get(string);
                String roadid =  jsonObject.getString("roadid");
                String roadapplication = jsonObject.getString("roadapplication");
                String roaddirection =  jsonObject.getString("roaddirection");
                double roadlength =  jsonObject.getDouble("roadlength");
                double roadlengths = Double.parseDouble(df.format(roadlength));
                String roadstartendlont =  jsonObject.getString("roadstartendlont");
                String roadName =  jsonObject.getString("roadName");
                String kind =  jsonObject.getString("kind");
                String serialNumber =  jsonObject.getString("serialNumber");
                if (roadid!=null||roadid!=""){
                    statement.setString(1,roadid);
                    statement.setString(2,roadapplication);
                    statement.setString(3,roaddirection);
                    statement.setDouble(4,roadlengths);
                    statement.setString(5,roadstartendlont);
                    statement.setString(6,roadName);
                    statement.setString(7,kind);
                    statement.setString(8,serialNumber);
                }
                statement.addBatch();
            }
            statement.executeBatch();
        } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeOracle(resultSet,statement,connection);
            closeOracle(resultSet3,statement3,connection3);
        }
    }

//    @Test
    //根据静态表数据合并出信号路段
    public void insertInto(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Connection connection2 = null;
        PreparedStatement statement2 = null;
        ResultSet resultSet2 = null;
        Connection connection3 = null;
        PreparedStatement statement3 = null;
        ResultSet resultSet3 = null;
        Connection connection4 = null;
        PreparedStatement statement4 = null;
        ResultSet resultSet4 = null;

        Set<String> roadnameSet = new HashSet<>();
        connection = getConnection();
        String sql1 = "select roadname from ROAD_PIECEWISE_DATA ";
        String  sql2 = "select id,roaddirection,roadlength,roadstartendlont,roadname,kind,serialnumber from ROAD_PIECEWISE_DATA where roadname like ? ";
        try {
            statement = connection.prepareStatement(sql1);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                String roadname = resultSet.getString("roadname");
                if (!roadname.split(",")[0].contains("辅路")){
                    roadnameSet.add(roadname.split(",")[0]);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeOracle(resultSet,statement,connection);
        }
        List<JSONObject> list = new ArrayList<>();
        List<String> roadidList = new ArrayList<>();
        HashSet<String> set = new HashSet<>();
        JSONObject idJson = new JSONObject();
        //遍历道路名称集合
        Iterator<String> iterator = roadnameSet.iterator();
        while (iterator.hasNext()){
            String next = iterator.next();//道路名称
            try {
                connection2 = getConnection();
                statement2 = connection2.prepareStatement(sql2);
                statement2.setString(1,next+",%");
                resultSet2 = statement2.executeQuery();
                JSONObject directonJson = new JSONObject();
                Set<String> strings = new HashSet<>();
                while (resultSet2.next()){
                    String roaddirection = resultSet2.getString("roaddirection");//路段方向
                    String roadstartendlont = resultSet2.getString("roadstartendlont");//经纬度
                    String roadname = resultSet2.getString("roadname");//路段名称
                    String kind = resultSet2.getString("kind");
                    String id = resultSet2.getString("id");
                    String serialnumber = resultSet2.getString("serialnumber");//路段顺序
                    double roadlength = resultSet2.getDouble("roadlength");//长度
                    if (directonJson.containsKey(roaddirection)){
                        List<JSONObject> jsonObjects = (List<JSONObject>) directonJson.get(roaddirection);
                        List<String> serialnumberes = (List<String>) directonJson.get(roaddirection+"number");
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("roadstartendlont",roadstartendlont);
                        jsonObject.put("roadname",roadname);
                        jsonObject.put("kind",kind);
                        jsonObject.put("id",id);
                        jsonObject.put("roadlength",roadlength);
                        jsonObject.put("serialnumber",serialnumber);
                        jsonObjects.add(jsonObject);
                        serialnumberes.add(serialnumber);
                        directonJson.put(roaddirection,jsonObjects);
                        directonJson.put(roaddirection+"number",serialnumberes);
                    }else {
                        List<JSONObject> jsonObjects = new ArrayList<>();
                        List<String> serialnumberes = new ArrayList<>();
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("roadstartendlont",roadstartendlont);
                        jsonObject.put("roadname",roadname);
                        jsonObject.put("kind",kind);
                        jsonObject.put("id",id);
                        jsonObject.put("roadlength",roadlength);
                        jsonObject.put("serialnumber",serialnumber);
                        jsonObjects.add(jsonObject);
                        serialnumberes.add(serialnumber);
                        directonJson.put(roaddirection,jsonObjects);
                        directonJson.put(roaddirection+"number",serialnumberes);
                    }
                    strings.add(roaddirection);
                }

                Iterator<String> iterator1 = strings.iterator();
                while (iterator1.hasNext()){
                    final String next1 = iterator1.next();
                    List<JSONObject> jsonObjects = (List<JSONObject>) directonJson.get(next1);
                    List<String> serialnumberes = (List<String>) directonJson.get(next1+"number");
                    if (!serialnumberes.contains("1")){
                        for (JSONObject jsonObject : jsonObjects) {
                            String roadname = jsonObject.getString("roadname");
                            String kind = jsonObject.getString("kind");
                            String id = jsonObject.getString("id");
                            double roadlength = jsonObject.getDouble("roadlength");
                            String roadName = roadname.split(",")[0]+"-"+roadname.split(",")[1]+"到"+roadname.split(",")[2]+next1;
                            String roadid = getPY(roadName);
                            double minTraveltime=0.0;
                            double maxTraveltime=0.0;
                            if (kind.equals("1")||kind.equals("2")||kind.equals("3")||kind.equals("4")){//min 8，max 70 速度
                                minTraveltime = (roadlength*1000)/(70/3.6);
                                maxTraveltime = (roadlength*1000)/(8/3.6);
                            }else {//min 5 ，max 60 速度
                                minTraveltime = (roadlength*1000)/(60/3.6);
                                maxTraveltime = (roadlength*1000)/(5/3.6);
                            }
                            idJson.put(roadid,","+id);
                            roadidList.add(roadid);
                            JSONObject roadJsonObject = new JSONObject();
                            roadJsonObject.put("roadname",roadName);
                            roadJsonObject.put("kind",kind);
                            roadJsonObject.put("roadlength",roadlength);
                            roadJsonObject.put("maxTraveltime",maxTraveltime);
                            roadJsonObject.put("minTraveltime",minTraveltime);
                            roadJsonObject.put("roaddirection",next1);
                            roadJsonObject.put("roadid",roadid);
                            list.add(roadJsonObject);
                        }
                    }else {
                        Collections.sort(jsonObjects, new Comparator<JSONObject>() {
                            @Override
                            public int compare(JSONObject o1, JSONObject o2) {
                                String serialnumber1 = o1.getString("serialnumber");
                                String serialnumber2 = o2.getString("serialnumber");
                                return serialnumber1.compareTo(serialnumber2);
                            }
                        });
                        String roadname1 = jsonObjects.get(0).getString("roadname");
                        String kind = jsonObjects.get(0).getString("kind");
                        String roadname4 = jsonObjects.get(jsonObjects.size()-1).getString("roadname");
                        String roadname = roadname1.split(",")[0]+"-"+roadname1.split(",")[1]+"到"+roadname4.split(",")[2]+next1;
                        double roadlengthAll = 0.0;
                        double roadlength1 = 0.0;
                        double roadlength2 = 0.0;
                        String roadnameFront = null;
                        String roadnamePost = null;
                        if (jsonObjects.size()>3){
                            String roadname2 = jsonObjects.get((jsonObjects.size()/2)-1).getString("roadname");
                            String roadname3 = jsonObjects.get(jsonObjects.size()/2).getString("roadname");
                            roadnameFront = roadname1.split(",")[0]+"-"+roadname1.split(",")[1]+"到"+roadname2.split(",")[2]+next1;
                            roadnamePost = roadname3.split(",")[0]+"-"+roadname3.split(",")[1]+"到"+roadname4.split(",")[2]+next1;
                        }
//
                        String idAll = "";
                        String idFront = "";
                        String idPost = "";
                        for (int i = 0; i < jsonObjects.size(); i++) {
                            double roadlength = jsonObjects.get(i).getDouble("roadlength");
                            String  id = jsonObjects.get(i).getString("id");
                            idAll = idAll +","+id;
                            roadlengthAll +=  roadlength;
                            if (jsonObjects.size()>3){
                                if (i<((jsonObjects.size()/2))){
                                    idFront = idFront +","+id;
                                    roadlength1+=roadlength;
                                }else {
                                    idPost = idPost +","+id;
                                    roadlength2+=roadlength;
                                }
                            }
                        }
                        double maxTraveltimeAll = 0.0;
                        double minTraveltimeAll = 0.0;
                        double maxTraveltime1 = 0.0;
                        double maxTraveltime2 = 0.0;
                        double minTraveltime1 = 0.0;
                        double minTraveltime2 = 0.0;
                        if (kind.equals("1")||kind.equals("2")||kind.equals("3")||kind.equals("4")){//min 8，max 70 速度
                            minTraveltimeAll = (roadlengthAll*1000)/(70/3.6);
                            maxTraveltimeAll = (roadlengthAll*1000)/(8/3.6);
                            if (jsonObjects.size()>3){
                                minTraveltime1 = (roadlength1*1000)/(70/3.6);
                                minTraveltime2 = (roadlength2*1000)/(70/3.6);
                                maxTraveltime1 = (roadlength1*1000)/(8/3.6);
                                maxTraveltime2 = (roadlength2*1000)/(8/3.6);
                            }
                        }else {//min 5 ，max 60 速度
                            minTraveltimeAll = (roadlengthAll*1000)/(60/3.6);
                            maxTraveltimeAll = (roadlengthAll*1000)/(5/3.6);
                            if (jsonObjects.size()>3){
                                minTraveltime1 = (roadlength1*1000)/(60/3.6);
                                minTraveltime2 = (roadlength2*1000)/(60/3.6);
                                maxTraveltime1 = (roadlength1*1000)/(5/3.6);
                                maxTraveltime2 = (roadlength2*1000)/(5/3.6);
                            }
                        }
                        roadname = roadname.replaceAll("1","");
                        String roadidAll = getPY(roadname);
                        idJson.put(roadidAll,idAll);
                        roadidList.add(roadidAll);
                        set.add(roadidAll);
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("roadname",roadname);
                        jsonObject.put("kind",kind);
                        jsonObject.put("roadlength",roadlengthAll);
                        jsonObject.put("maxTraveltime",maxTraveltimeAll);
                        jsonObject.put("minTraveltime",minTraveltimeAll);
                        jsonObject.put("roaddirection",next1);
                        jsonObject.put("roadid",roadidAll);
                        list.add(jsonObject);
                        if (jsonObjects.size()>3){
                            String roadidFront = getPY(roadnameFront);
                            String roadidPost = getPY(roadnamePost);
                            idJson.put(roadidFront,idFront);
                            idJson.put(roadidPost,idPost);
                            roadidList.add(roadidFront);
                            roadidList.add(roadidPost);
                            set.add(roadidFront);
                            set.add(roadidPost);
                            JSONObject jsonObject1 = new JSONObject();
                            JSONObject jsonObject2= new JSONObject();
                            jsonObject1.put("roadname",roadnameFront);
                            jsonObject1.put("kind",kind);
                            jsonObject1.put("roadlength",roadlength1);
                            jsonObject1.put("maxTraveltime",maxTraveltime1);
                            jsonObject1.put("minTraveltime",minTraveltime1);
                            jsonObject1.put("roaddirection",next1);
                            jsonObject1.put("roadid",roadidFront);

                            jsonObject2.put("roadname",roadnamePost);
                            jsonObject2.put("kind",kind);
                            jsonObject2.put("roadlength",roadlength2);
                            jsonObject2.put("maxTraveltime",maxTraveltime2);
                            jsonObject2.put("minTraveltime",minTraveltime2);
                            jsonObject2.put("roaddirection",next1);
                            jsonObject2.put("roadid",roadidPost);
                            list.add(jsonObject1);
                            list.add(jsonObject2);
                        }
                    }

                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                closeOracle(resultSet2,statement2,connection2);
            }
        }

        connection3 = getConnection();
        String  sql3 = "insert into SWAN_ROAD_PIECEWISE_MIDDLE(" +
                "ROADID,id" +
                ") values (?,?)";
        try {
            statement3 = connection3.prepareStatement(sql3);
            for (String roadid : roadidList) {
                String ides = idJson.getString(roadid);
                String[] split = ides.split(",");
                for (int i = 1; i < split.length; i++) {
                    statement3.setString(1,roadid);
                    statement3.setString(2,split[i]);
                    statement3.addBatch();
                }
            }
            statement3.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeOracle(resultSet3,statement3,connection3);
        }

        connection4 = getConnection();
        String  sql4 = "insert into road_piecewise_data_new_t (" +
                "ROADID,ROADNAME,ROADDIRECTION,ROADLENGTH,MAXTRAVELTIME,MINTRAVELTIME,KIND" +
                ") values (?,?,?,?,?,?,?)";
        try {
            DecimalFormat df = new DecimalFormat("#.#");
            DecimalFormat df2 = new DecimalFormat("#.###");
            statement4 = connection4.prepareStatement(sql4);
            for (JSONObject jsonObject : list) {
                String roadid = jsonObject.getString("roadid");
                String roadname = jsonObject.getString("roadname");
                String roaddirection = jsonObject.getString("roaddirection");
                double roadlength = Double.parseDouble(df2.format(jsonObject.getDouble("roadlength")));
                double maxTraveltime = Double.parseDouble(df.format(jsonObject.getDouble("maxTraveltime")));
                double minTraveltime = Double.parseDouble(df.format(jsonObject.getDouble("minTraveltime")));
                String kind = jsonObject.getString("kind");
                statement4.setString(1,roadid);
                statement4.setString(2,roadname);
                statement4.setString(3,roaddirection);
                statement4.setDouble(4,roadlength);
                statement4.setDouble(5,maxTraveltime);
                statement4.setDouble(6,minTraveltime);
                statement4.setString(7,kind);
                statement4.addBatch();
            }
            statement4.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeOracle(resultSet4,statement4,connection4);
        }
    }
//
//
//    @Test
//    public void insert(){
//        Connection connection = null;
//        PreparedStatement statement = null;
//        ResultSet resultSet = null;
//        Connection connection2 = null;
//        PreparedStatement statement2 = null;
//        ResultSet resultSet2 = null;
//        Connection connection3 = null;
//        PreparedStatement statement3 = null;
//        ResultSet resultSet3 = null;
//        Connection connection4 = null;
//        PreparedStatement statement4 = null;
//        ResultSet resultSet4 = null;
//
//
//        Set<String> roadnameSet = new HashSet<>();
//        connection = getConnection();
//        String sql1 = "select roadname from ROAD_PIECEWISE_DATA ";
//        String  sql2 = "select id,roaddirection,roadlength,roadstartendlont,roadname,kind from ROAD_PIECEWISE_DATA where roadname like ?  order by roaddirection desc";
//        try {
//            statement = connection.prepareStatement(sql1);
//            resultSet = statement.executeQuery();
//            while (resultSet.next()){
//                String roadname = resultSet.getString("roadname");
//                if (!roadname.contains("辅路")){
//                    roadnameSet.add(roadname.split(",")[0]);
//                }
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }finally {
//            closeOracle(resultSet,statement,connection);
//        }
//        List<JSONObject> list = new ArrayList<>();
//        List<String> roadidList = new ArrayList<>();
//        JSONObject idJson = new JSONObject();
//        Iterator<String> iterator = roadnameSet.iterator();
//        while (iterator.hasNext()){
//            String next = iterator.next();
//                try {
//                    connection2 = getConnection();
//                    statement2 = connection2.prepareStatement(sql2);
//                    statement2.setString(1,next+",%");
//                    resultSet2 = statement2.executeQuery();
//                    JSONObject directonJson = new JSONObject();
//                    Set<String> strings = new HashSet<>();
//                    while (resultSet2.next()){
//                        String roaddirection = resultSet2.getString("roaddirection");
//                        String roadstartendlont = resultSet2.getString("roadstartendlont");
//                        String roadname = resultSet2.getString("roadname");
//                        String kind = resultSet2.getString("kind");
//                        String id = resultSet2.getString("id");
//                        double roadlength = resultSet2.getDouble("roadlength");
//                        if (directonJson.containsKey(roaddirection)){
//                            List<JSONObject> jsonObjects = (List<JSONObject>) directonJson.get(roaddirection);
//                            JSONObject jsonObject = new JSONObject();
//                            jsonObject.put("roadstartendlont",roadstartendlont);
//                            jsonObject.put("roadname",roadname);
//                            jsonObject.put("kind",kind);
//                            jsonObject.put("id",id);
//                            jsonObject.put("roadlength",roadlength);
//                            jsonObjects.add(jsonObject);
//                            directonJson.put(roaddirection,jsonObjects);
//                        }else {
//                            List<JSONObject> jsonObjects = new ArrayList<>();
//                            JSONObject jsonObject = new JSONObject();
//                            jsonObject.put("roadstartendlont",roadstartendlont);
//                            jsonObject.put("roadname",roadname);
//                            jsonObject.put("kind",kind);
//                            jsonObject.put("id",id);
//                            jsonObject.put("roadlength",roadlength);
//                            jsonObjects.add(jsonObject);
//                            directonJson.put(roaddirection,jsonObjects);
//                        }
//                        strings.add(roaddirection);
//                    }
//                    Iterator<String> iterator1 = strings.iterator();
//                    while (iterator1.hasNext()){
//                        final String next1 = iterator1.next();
//                        List<JSONObject> jsonObjects = (List<JSONObject>) directonJson.get(next1);
//                        Collections.sort(jsonObjects, new Comparator<JSONObject>() {
//                            @Override
//                            public int compare(JSONObject o1, JSONObject o2) {
//                                int lont=0;
//                                if (next1.equals("W-E")){//经度小到大
//                                    String roadstartendlonts1 = o1.getString("roadstartendlont");
//                                    String roadstartendlonts2 = o2.getString("roadstartendlont");
//                                    String roadstartendlont1 = roadstartendlonts1.split(" ")[0];
//                                    String roadstartendlont2 = roadstartendlonts2.split(" ")[0];
//                                    Double lont1 = Double.parseDouble(roadstartendlont1.split(";")[0]);
//                                    Double lont2 = Double.parseDouble(roadstartendlont2.split(";")[0]);
//                                    lont = lont1.compareTo(lont2);
//                                }else if (next1.equals("E-W")){
//                                    String roadstartendlonts1 = o1.getString("roadstartendlont");
//                                    String roadstartendlonts2 = o2.getString("roadstartendlont");
//                                    String roadstartendlont1 = roadstartendlonts1.split(" ")[0];
//                                    String roadstartendlont2 = roadstartendlonts2.split(" ")[0];
//                                    Double lont1 = Double.parseDouble(roadstartendlont1.split(";")[0]);
//                                    Double lont2 = Double.parseDouble(roadstartendlont2.split(";")[0]);
//                                    lont = lont2.compareTo(lont1);
//                                }else if (next1.equals("S-N")){//纬度小到大
//                                    String roadstartendlonts1 = o1.getString("roadstartendlont");
//                                    String roadstartendlonts2 = o2.getString("roadstartendlont");
//                                    String roadstartendlont1 = roadstartendlonts1.split(" ")[0];
//                                    String roadstartendlont2 = roadstartendlonts2.split(" ")[0];
//                                    Double lont1 = Double.parseDouble(roadstartendlont1.split(";")[1]);
//                                    Double lont2 = Double.parseDouble(roadstartendlont2.split(";")[1]);
//                                    lont= lont1.compareTo(lont2);
//                                }else if (next1.equals("N-S")){
//                                    String roadstartendlonts1 = o1.getString("roadstartendlont");
//                                    String roadstartendlonts2 = o2.getString("roadstartendlont");
//                                    String roadstartendlont1 = roadstartendlonts1.split(" ")[0];
//                                    String roadstartendlont2 = roadstartendlonts2.split(" ")[0];
//                                    Double lont1 = Double.parseDouble(roadstartendlont1.split(";")[1]);
//                                    Double lont2 = Double.parseDouble(roadstartendlont2.split(";")[1]);
//                                    lont= lont2.compareTo(lont1);
//                                }
//                                return lont;
//                            }
//                        });
//                        String roadname1 = jsonObjects.get(0).getString("roadname");
//                        String kind = jsonObjects.get(0).getString("kind");
//                        String roadname4 = jsonObjects.get(jsonObjects.size()-1).getString("roadname");
//                        String roadname = roadname1.split(",")[0]+"-"+roadname1.split(",")[1]+"到"+roadname4.split(",")[2];
//                        double roadlengthAll = 0.0;
//                        double roadlength1 = 0.0;
//                        double roadlength2 = 0.0;
//                        String roadnameFront = null;
//                        String roadnamePost = null;
//                        if (jsonObjects.size()>3){
//                            String roadname2 = jsonObjects.get((jsonObjects.size()/2)-1).getString("roadname");
//                            String roadname3 = jsonObjects.get(jsonObjects.size()/2).getString("roadname");
//                            roadnameFront = roadname1.split(",")[0]+"-"+roadname1.split(",")[1]+"到"+roadname2.split(",")[2];
//                            roadnamePost = roadname3.split(",")[0]+"-"+roadname3.split(",")[1]+"到"+roadname4.split(",")[2];
//                        }
////
//                        String idAll = "";
//                        String idFront = "";
//                        String idPost = "";
//                        for (int i = 0; i < jsonObjects.size(); i++) {
//                            double roadlength = jsonObjects.get(i).getDouble("roadlength");
//                            String  id = jsonObjects.get(i).getString("id");
//                            idAll = idAll +","+id;
//                            roadlengthAll +=  roadlength;
//                            if (jsonObjects.size()>3){
//                                if (i<((jsonObjects.size()/2)-1)){
//                                    idFront = idFront +","+id;
//                                    roadlength1+=roadlength;
//                                }else {
//                                    idPost = idPost +","+id;
//                                    roadlength2+=roadlength;
//                                }
//                            }
//                        }
//                        int maxTraveltimeAll = 0;
//                        int minTraveltimeAll = 0;
//                        int maxTraveltime1 = 0;
//                        int maxTraveltime2 = 0;
//                        int minTraveltime1 = 0;
//                        int minTraveltime2 = 0;
//                        if (kind.equals("1")||kind.equals("2")||kind.equals("3")||kind.equals("4")){//min 8，max 70 速度
//                             maxTraveltimeAll = (int)((roadlengthAll*1000)/(70/3.6));
//                             minTraveltimeAll = (int)((roadlengthAll*1000)/(8/3.6));
//                            if (jsonObjects.size()>3){
//                                 maxTraveltime1 = (int)((roadlength1*1000)/(70/3.6));
//                                 maxTraveltime2 = (int)((roadlength2*1000)/(70/3.6));
//                                 minTraveltime1 = (int)((roadlength1*1000)/(8/3.6));
//                                 minTraveltime2 = (int)((roadlength2*1000)/(8/3.6));
//                            }
//                        }else {//min 5 ，max 60 速度
//                             maxTraveltimeAll = (int)((roadlengthAll*1000)/(60/3.6));
//                             minTraveltimeAll = (int)((roadlengthAll*1000)/(5/3.6));
//                            if (jsonObjects.size()>3){
//                                 maxTraveltime1 = (int)((roadlength1*1000)/(60/3.6));
//                                 maxTraveltime2 = (int)((roadlength2*1000)/(60/3.6));
//                                 minTraveltime1 = (int)((roadlength1*1000)/(5/3.6));
//                                 minTraveltime2 = (int)((roadlength2*1000)/(5/3.6));
//                            }
//                        }
//                        String roadidAll = getPY(roadname)+next1;
//                        idJson.put(roadidAll,idAll);
//                        roadidList.add(roadidAll);
//                        JSONObject jsonObject = new JSONObject();
//                        jsonObject.put("roadname",roadname);
//                        jsonObject.put("kind",kind);
//                        jsonObject.put("roadlength",roadlengthAll);
//                        jsonObject.put("maxTraveltime",maxTraveltimeAll);
//                        jsonObject.put("minTraveltime",minTraveltimeAll);
//                        jsonObject.put("roaddirection",next1);
//                        jsonObject.put("roadid",roadidAll);
//                        list.add(jsonObject);
//                        if (jsonObjects.size()>3){
//                            String roadidFront = getPY(roadnameFront)+next1;
//                            String roadidPost = getPY(roadnamePost)+next1;
//                            idJson.put(roadidFront,idFront);
//                            idJson.put(roadidPost,idPost);
//                            roadidList.add(roadidFront);
//                            roadidList.add(roadidPost);
//                            JSONObject jsonObject1 = new JSONObject();
//                            JSONObject jsonObject2= new JSONObject();
//                            jsonObject1.put("roadname",roadnameFront);
//                            jsonObject1.put("kind",kind);
//                            jsonObject1.put("roadlength",roadlength1);
//                            jsonObject1.put("maxTraveltime",maxTraveltime1);
//                            jsonObject1.put("minTraveltime",minTraveltime1);
//                            jsonObject1.put("roaddirection",next1);
//                            jsonObject1.put("roadid",roadidFront);
//
//                            jsonObject2.put("roadname",roadnamePost);
//                            jsonObject2.put("kind",kind);
//                            jsonObject2.put("roadlength",roadlength2);
//                            jsonObject2.put("maxTraveltime",maxTraveltime2);
//                            jsonObject2.put("minTraveltime",minTraveltime2);
//                            jsonObject2.put("roaddirection",next1);
//                            jsonObject2.put("roadid",roadidPost);
//                            list.add(jsonObject1);
//                            list.add(jsonObject2);
//                        }
//                    }
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }finally {
//                    closeOracle(resultSet2,statement2,connection2);
//                }
//        }
//
//        connection3 = getConnection();
//        String  sql3 = "insert into SWAN_ROAD_PIECEWISE_MIDDLE_NEW (" +
//                "ROADID,id" +
//                ") values (?,?)";
//        try {
//            statement3 = connection3.prepareStatement(sql3);
//            for (String roadid : roadidList) {
//                String ides = idJson.getString(roadid);
//                String[] split = ides.split(",");
//                for (int i = 1; i < split.length; i++) {
//                    statement3.setString(1,roadid);
//                    statement3.setString(2,split[i]);
//                    statement3.addBatch();
//                }
//            }
//            statement3.executeBatch();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }finally {
//            closeOracle(resultSet3,statement3,connection3);
//        }
//
//        connection4 = getConnection();
//        String  sql4 = "insert into road_piecewise_data_new (" +
//                "ROADID,ROADNAME,ROADDIRECTION,ROADLENGTH,MAXTRAVELTIME,MINTRAVELTIME,KIND" +
//                ") values (?,?,?,?,?,?,?)";
//        try {
//            statement4 = connection4.prepareStatement(sql4);
//            for (JSONObject jsonObject : list) {
//                String roadid = jsonObject.getString("roadid");
//                String roadname = jsonObject.getString("roadname");
//                String roaddirection = jsonObject.getString("roaddirection");
//                String roadlength = jsonObject.getString("roadlength");
//                String maxTraveltime = jsonObject.getString("maxTraveltime");
//                String minTraveltime = jsonObject.getString("minTraveltime");
//                String kind = jsonObject.getString("kind");
//                statement4.setString(1,roadid);
//                statement4.setString(2,roadname);
//                statement4.setString(3,roaddirection);
//                statement4.setString(4,roadlength);
//                statement4.setString(5,maxTraveltime);
//                statement4.setString(6,minTraveltime);
//                statement4.setString(7,kind);
//                statement4.addBatch();
//            }
//            statement4.executeBatch();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }finally {
//            closeOracle(resultSet4,statement4,connection4);
//        }
//    }




    public String getPY(String src){
        char[] t1 = null;
        t1 = src.toCharArray();
        String[] t2 = new String[t1.length];
        // 设置汉字拼音输出的格式
        HanyuPinyinOutputFormat t3 = new HanyuPinyinOutputFormat();
        t3.setCaseType(HanyuPinyinCaseType.LOWERCASE);
        t3.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
        t3.setVCharType(HanyuPinyinVCharType.WITH_V);
        String t4 = "";
        int t0 = t1.length;
        try {
            for (int i =0; i < t0; i++) {
                // 判断能否为汉字字符
                if (Character.toString(t1[i]).matches("[\\u4E00-\\u9FA5]+")) {
                    t2 = PinyinHelper.toHanyuPinyinStringArray(t1[i], t3);// 将汉字的几种全拼都存到t2数组中
                    t4 += t2[0];// 取出该汉字全拼的第一种读音并连接到字符串t4后
                } else {
                    // 如果不是汉字字符，间接取出字符并连接到字符串t4后
                    t4 += Character.toString(t1[i]);
                }
            }
            System.out.println(t4);
        } catch (BadHanyuPinyinOutputFormatCombination e) {
            e.printStackTrace();
        }
        return t4;
    }
}

